﻿
CREATE PROCEDURE [dbo].[MngChangeAssortment]
(--Замена кодов препаратов
	@FromId int,	--код препарата, который, возможно, будет удалён из бд
	@ToId int			--код препарата, который подставляется вместо @FromId
)
AS
DECLARE @intGoodsId int, @intManufacturerId int, @intCountryId int, @intRetailPack int, @intParentId int, @intChildId int
DECLARE @decFixedPrice decimal(19, 2), @intWholesalePack int, @strManufactureBarCode varchar(13), @bitAllowUnPack bit
DECLARE @intParentRetailPack int, @datNow datetime, @strMsg as varchar(500), @intRet int

DECLARE @intNewGoodsId int

SET @strMsg = 'Замена препарата с ' + cast(@FromId as varchar(10)) + ' на ' + cast(@ToId as varchar(10))
PRINT @strMsg

IF (@FromId Is Null) OR (@ToId Is Null)
BEGIN
	PRINT '!-------------------- Не указаны коды препаратов для замены !'
	RETURN 0
END

IF (@FromId = @ToId )
BEGIN
	PRINT '!-------------------- Оба кода препаратов для замены равны ! ' + cast(@FromId as varchar(10)) + ' = ' + cast(@ToId as varchar(10)) 
	RETURN 0
END

IF NOT EXISTS (SELECT * FROM CA.Assortment WHERE Id = @FromId AND ToDelete = 0) 
BEGIN
	PRINT '!-------------------- Отсутствует УДАЛЯЕМЫЙ препарат с кодом ' + cast(@FromId as varchar(10))
	RETURN 0
END

IF NOT EXISTS (SELECT * FROM CA.Assortment WHERE Id = @ToId AND ToDelete = 0) 
BEGIN
	PRINT '!-------------------- Отсутствует ЗАМЕНЯЕМЫЙ препарат с кодом ' + cast(@ToId as varchar(10))
	RETURN 0
END

DECLARE ChangeLines CURSOR FAST_FORWARD FOR
SELECT Id, ManufacturerId, CountryId, RetailPack, ChildId, FixedPrice, WholesalePack, ManufactureBarCode, AllowUnPack
FROM CA.Goods WHERE AssortmentId = @FromId AND ToDelete = 0

OPEN ChangeLines

FETCH NEXT FROM ChangeLines INTO @intGoodsId, @intManufacturerId, @intCountryId, @intRetailPack, @intChildId, 
@decFixedPrice, @intWholesalePack, @strManufactureBarCode, @bitAllowUnPack

WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'Найден УДАЛЯЕМЫЙ товар с кодом ' + cast(@intGoodsId as varchar(10))
	SET @intNewGoodsId = null

--найти аналогичный товар с другим кодом препарата
	SELECT @intNewGoodsId = Id FROM CA.Goods WHERE AssortmentId = @ToId 
	AND ManufacturerId	= @intManufacturerId
	AND CountryId				= @intCountryId
	AND RetailPack			= @intRetailPack
--	AND ( (ParentId Is Null AND @intParentId Is Null) OR (ParentId Is NOT Null AND @intParentId Is NOT Null) )
	AND ToDelete = 0

	IF @intNewGoodsId Is Null
	BEGIN  --аналогичного товара НЕТ
		SET @datNow = getdate()

EXECUTE @intRet = [dbo].[GoodsInsert] 
		 @AssortmentId = @ToId
		,@ManufacturerId = @intManufacturerId
		,@CountryId = @intCountryId
		,@WholesalePack = @intWholesalePack
		,@RetailPack = @intRetailPack
		,@ChildId = Null
		,@AllowUnPack = @bitAllowUnPack
		,@ManufactureBarCode = @strManufactureBarCode
		,@FixedPrice = @decFixedPrice
		,@DefekturaAggregate = 0
		,@Note = null
		,@MD = @datNow
		,@MW = 'Смена препарата'

		IF @intRet <> 0 
		BEGIN
			PRINT 'ОШИБКА при создании нового товара ' + cast(@intRet as varchar(10))
			CLOSE ChangeLines
			DEALLOCATE ChangeLines
			RETURN @intRet
		END

		SELECT @intNewGoodsId = max(Id) FROM CA.Goods  --#NewGoods
		PRINT 'Добавлен новый товар ' + cast(@intNewGoodsId as varchar(10))

	END ELSE BEGIN
		PRINT 'Найден аналогичный товар ' + cast(@intNewGoodsId as varchar(10))
	END

--заменяем старый код товара на новый в транзакционных таблицах
	EXEC dbo.MngChangeGoods @intGoodsId, @intNewGoodsId

PRINT 'Удаляем товар ' + cast(@intGoodsId as varchar(10))
	IF EXISTS (SELECT Id FROM CO.Departments WHERE Id > 1)
	BEGIN  --есть офис и аптеки
		IF  EXISTS (SELECT * FROM PR.DepartmentOptions WHERE DepartmentId = 0)  --скрипт запущен в офисе
			UPDATE CA.Goods SET ToDelete = 1, MW = 'Смена препарата', MD = getdate() WHERE Id = @intGoodsId
		ELSE --скрипт запущен в аптеке
			DELETE FROM CA.Goods WHERE Id = @intGoodsId

	END ELSE BEGIN --только одна аптека
		DELETE FROM CA.Goods WHERE Id = @intGoodsId
	END


FETCH NEXT FROM ChangeLines INTO @intGoodsId, @intManufacturerId, @intCountryId, @intRetailPack, @intChildId, 
@decFixedPrice, @intWholesalePack, @strManufactureBarCode, @bitAllowUnPack
END

CLOSE ChangeLines
DEALLOCATE ChangeLines

PRINT 'Удаляем препарат ' + cast(@FromId as varchar(10))
IF EXISTS (SELECT Id FROM CO.Departments WHERE Id > 1)
BEGIN  --есть офис и аптеки
	IF  EXISTS (SELECT * FROM PR.DepartmentOptions WHERE DepartmentId = 0)  --скрипт запущен в офисе
		UPDATE CA.Assortment SET ToDelete = 1, MW = 'Смена препарата', MD = getdate() WHERE Id = @FromId
	ELSE --скрипт запущен в аптеке
		DELETE FROM CA.Assortment WHERE Id = @FromId

END ELSE BEGIN --только одна аптека
	DELETE FROM CA.Assortment WHERE Id = @FromId
END

RETURN 0
